package com.lanqiao.guimei.dao;

import com.lanqiao.guimei.commons.JdbcUtil;
import com.lanqiao.guimei.commons.SaleProdListHandler;
import com.lanqiao.guimei.vo.*;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author:沈林玉
 * @date:2019/8/16 9:01
 * @Description:
 */
public class SaleProdMainDao {
    QueryRunner runner = new QueryRunner(true);
    Logger logger = Logger.getLogger(SaleProdMainDao.class);

    /**
     * 1.在线商品查询,分页显示
     * @param page
     * @return
     */
    public List<SaleProd> selectSaleProd(Page<SaleProd> page) {

        String sql = "select s.id id,s.prod_no prodNo,s.saler_id salerId,s.stk_id stkId,\n" +
                "s.real_price realPrice,s.carriage carriage,s.star_time starTime,s.end_time endTime,s.prod_skin prodSkin,s.location,s.status,s.rn\n" +
                "from (select t.*,ROWNUM rn from (select * from sale_prod order by id) t where ROWNUM<=? and t.status=1) s\n" +
                "where rn>=?";
        try (Connection conn = JdbcUtil.getConnection()) {
            return runner.query(conn,sql,new BeanListHandler<>(SaleProd.class),page.getLastIndex(),page.getFirstIndex());
        } catch (SQLException e) {
            logger.error("在线商品分页查询失败。。。"+e.getMessage());
        }
        return null;
    }

    /**
     * 获取总记录数
     * @param page
     * @return
     */
    public int getCountSaleProd(Page<SaleProd> page){
        String sql = "select count(1) ptn from sale_prod";
        try (Connection conn = JdbcUtil.getConnection()) {
            return runner.query(conn,sql,new ScalarHandler<BigDecimal>("ptn")).intValue();
        } catch (SQLException e) {
            logger.error("获取在线商品总记录数失败。。。"+e.getMessage());
        }
        return 0;
    }

    /**
     * 1.按商品编号查询在线商品
     * @param saleProd
     * @return
     */
    public List<SaleProd> selectSaleProdByProdNo(SaleProd saleProd) {
        String sql = "select s.* from sale_prod s where s.prod_no = ?";
        try (Connection conn = JdbcUtil.getConnection()) {
            List<SaleProd> saleProdList = runner.query(conn, sql, new BeanListHandler<>(SaleProd.class), saleProd.getProdNo());
            return saleProdList;

        } catch (SQLException e) {
            logger.error("在售商品按商品编号查询失败。。。"+e.getMessage());
        }

        return null;
    }

    /**
     * 2.按卖家查询在线商品
     * @param saleProd
     * @return
     */
    public List<SaleProd> selectSaleProdBySalerName(SaleProd saleProd) {
        String sql = "select id,prod_no prodNo,saler_id salerId,stk_id stkId,real_price realPrice," +
                "carriage,star_time starTime,end_time endTime,prod_skin prodSkin,location,status from sale_prod where saler_id=" +
                "(select user_id from guimei_users where login_name like ?) ";
        try (Connection conn = JdbcUtil.getConnection()) {
            List<SaleProd> saleProdList = runner.query(conn, sql, new BeanListHandler<>(SaleProd.class), "%"+saleProd.getGuimeiUsers().getLoginName()+"%");
            return saleProdList;

        } catch (SQLException e) {
            logger.error("在售商品卖家查询失败。。。"+e.getMessage());
        }

        return null;
    }
    /**
     * 3.按一口价查询在线商品
     * @param saleProd
     * @return
     */
    public List<SaleProd> selectSaleProdByRealPrice(SaleProd saleProd) {
        String sql = "select id,prod_no prodNo,saler_id salerId,stk_id stkId,real_price realPrice," +
                "carriage,star_time starTime,end_time endTime,prod_skin prodSkin,location,status " +
                "from sale_prod where real_price = ?";
        try (Connection conn = JdbcUtil.getConnection()) {
            List<SaleProd> saleProdList = runner.query(conn, sql, new BeanListHandler<>(SaleProd.class), saleProd.getRealPrice());
            return saleProdList;
        } catch (SQLException e) {
            logger.error("在售商品一口价查询失败。。。"+e.getMessage());
        }
        return null;
    }

    /**
     * 4.按开始售卖时间查询在线商品
     * @param saleProd
     * @return
     */
    public List<SaleProd> selectSaleProdByStartTime(SaleProd saleProd) {
        String sql = "select id,prod_no prodNo,saler_id salerId,stk_id stkId,real_price realPrice," +
                "carriage,star_time starTime,end_time endTime,prod_skin prodSkin,location,status " +
                "from sale_prod where star_time >=?";
        try (Connection conn = JdbcUtil.getConnection()) {
            List<SaleProd> saleProdList = runner.query(conn, sql, new BeanListHandler<>(SaleProd.class), saleProd.getStarTime());
            return saleProdList;

        } catch (SQLException e) {
            logger.error("在售商品开始售卖时间查询失败。。。"+e.getMessage());
        }

        return null;
    }

    /**
     * 5.按结束售卖时间查询在线商品
     * @param saleProd
     * @return
     */
    public List<SaleProd> selectSaleProdByEndTime(SaleProd saleProd) {
        String sql = "select id,prod_no prodNo,saler_id salerId,stk_id stkId,real_price realPrice," +
                "carriage,star_time starTime,end_time endTime,prod_skin prodSkin,location,status " +
                "from sale_prod where end_time >=?";
        try (Connection conn = JdbcUtil.getConnection()) {
            List<SaleProd> saleProdList = runner.query(conn, sql, new BeanListHandler<>(SaleProd.class), saleProd.getEndTime());
            return saleProdList;

        } catch (SQLException e) {
            logger.error("在售商品结束售卖时间查询失败。。。"+e.getMessage());
        }

        return null;
    }

    /**
     * 6.按起始数量查询在线商品
     * @param
     * @return
     */
    public List<SaleProd> selectSaleProdByStartQuantity(SaleProd saleProd,Stock stock) {
        String sql = "select s.id,s.prod_no prodNo,s.saler_id salerId,s.stk_id stkId,s.real_price realPrice," +
                "s.carriage,s.star_time starTime,s.end_time endTime,s.prod_skin prodSkin,s.location,s.status " +
                "from sale_prod s,stock t where s.stk_id =t.stk_id and t.quantity >=?";
        try (Connection conn = JdbcUtil.getConnection()) {
            List<SaleProd> saleProdList = runner.query(conn, sql, new BeanListHandler<>(SaleProd.class), stock.getQuantity());
            return saleProdList;

        } catch (SQLException e) {
            logger.error("在售商品起始数量查询失败。。。"+e.getMessage());
        }

        return null;
    }
    /**
     * 7.按结束数量查询在线商品
     * @param saleProd
     * @return
     */
    public List<SaleProd> selectSaleProdByEndQuantity(SaleProd saleProd) {
        String sql = "select s.id,s.prod_no prodNo,s.saler_id salerId,s.stk_id stkId,s.real_price realPrice," +
                "s.carriage,s.star_time starTime,s.end_time endTime,s.prod_skin prodSkin,s.location,s.status " +
                "from sale_prod s，stock t where s.stk_id =t.stk_id and t.quantity <=?";
        try (Connection conn = JdbcUtil.getConnection()) {
            List<SaleProd> saleProdList = runner.query(conn, sql, new BeanListHandler<>(SaleProd.class), saleProd.getGuimeiUsers().getLoginName());
            return saleProdList;
        } catch (SQLException e) {
            logger.error("在售商品起始数量查询失败。。。"+e.getMessage());
        }
        return null;
    }

    /**
     * 8.按运费查询在线商品
     * @param saleProd
     * @return
     */
    public List<SaleProd> selectSaleProdByCarriage(SaleProd saleProd) {
        String sql = "select select s.id,s.prod_no prodNo,s.saler_id salerId,s.stk_id stkId,s.real_price realPrice," +
                "s.carriage,s.star_time starTime,s.end_time endTime,s.prod_skin prodSkin,s.location,s.status  " +
                "from sale_prod s where s.carriage=?";
        try (Connection conn = JdbcUtil.getConnection()) {
            List<SaleProd> saleProdList = runner.query(conn, sql, new BeanListHandler<>(SaleProd.class), saleProd.getCarriage());
            return saleProdList;

        } catch (SQLException e) {
            logger.error("在售商品运费查询失败。。。"+e.getMessage());
        }

        return null;
    }

    /**
     * 9.按商品类型查询在线商品
     * @param saleProd
     * @return
     */
    public List<SaleProd> selectSaleProdByProdSkin(SaleProd saleProd) {
        String sql = "select select s.id,s.prod_no prodNo,s.saler_id salerId,s.stk_id stkId,s.real_price realPrice," +
                "s.carriage,s.star_time starTime,s.end_time endTime,s.prod_skin prodSkin,s.location,s.status  " +
                "from sale_prod s where s.prod_skin like ?";
        try (Connection conn = JdbcUtil.getConnection()) {
            List<SaleProd> saleProdList = runner.query(conn, sql, new BeanListHandler<>(SaleProd.class), "%"+saleProd.getProdSkin()+"%");
            return saleProdList;

        } catch (SQLException e) {
            logger.error("在售商品商品类型查询失败。。。"+e.getMessage());
        }

        return null;
    }

    /**
     * 10.按地址查询在线商品
     * @param saleProd
     * @return
     */
    public List<SaleProd> selectSaleProdByLocation(SaleProd saleProd) {
        String sql = "select select s.id,s.prod_no prodNo,s.saler_id salerId,s.stk_id stkId,s.real_price realPrice," +
                "s.carriage,s.star_time starTime,s.end_time endTime,s.prod_skin prodSkin,s.location,s.status  " +
                "from sale_prod s where s.location like ?";
        try (Connection conn = JdbcUtil.getConnection()) {
            List<SaleProd> saleProdList = runner.query(conn, sql, new BeanListHandler<>(SaleProd.class), "%"+saleProd.getLocation()+"%");
            return saleProdList;
        } catch (SQLException e) {
            logger.error("在售商品地址查询失败。。。"+e.getMessage());
        }

        return null;
    }

    /**
     * 11.按条件查询在线商品
     * @param
     * @return
     */
    public List<SaleProd> selectSaleProdByCondition(Page<SaleProd> page) {
        SaleProd saleProd = page.getQueryObject();
        List saleProdList = new ArrayList();
        StringBuffer sql = new StringBuffer(
                "select sale.* " +
                        "from (select r.*, ROWNUM rn" +
                        "      from (select " +
                        "                   s.id," +
                        "                   s.prod_no    prodNo," +
                        "                   s.saler_id   salerId," +
                        "                   s.stk_id     stkId," +
                        "                   s.real_price realPrice," +
                        "                   s.carriage   carriage," +
                        "                   s.star_time  starTime," +
                        "                   s.end_time   endTime," +
                        "                   s.prod_skin  prodSkin," +
                        "                   s.location," +
                        "                   s.status," +
                        "                   stk.STK_NAME   stkName," +
                        "                   prod.PROD_NAME  prodName," +
                        "                   saler.LOGIN_NAME loginName" +
                        "            from sale_prod s," +
                        "                 product prod," +
                        "                 guimei_users saler," +
                        "                 stock stk" +
                        "            where s.PROD_NO = prod.PROD_NO" +
                        "              and s.SALER_ID = saler.USER_ID" +
                        "              and s.STK_ID = stk.STK_ID ");
        if (saleProd.getGuimeiUsers()!= null
                && StringUtils.isNotBlank(saleProd.getGuimeiUsers().getLoginName())){
            sql.append(" and sale.saler_id=" +
                    "(select user_id from guimei_users where login_name like ? )");
            saleProdList.add("%"+saleProd.getGuimeiUsers().getLoginName()+"%");
        }
        if (saleProd.getRealPrice()!=null){
            sql.append(" and s.real_price = ? ");
            saleProdList.add(saleProd.getRealPrice());
        }
        if (saleProd.getStarTime()!=null){
            sql.append(" and s.star_time >=? ");
            saleProdList.add(saleProd.getStarTime());
        }
        if (saleProd.getEndTime()!=null){
            sql.append(" and s.end_time >=? ");
            saleProdList.add(saleProd.getEndTime());
        }
        if (saleProd.getStock()!=null &&
                saleProd.getStock().getQuantity()!=null){
            sql.append(" and s.stk_id=" +
                    " (select stk_id from stock where quantity >=? ");
            saleProdList.add(saleProd.getStock().getQuantity());
        }
        if (saleProd.getCarriage()!=null && saleProd.getCarriage()!=0){
            sql.append(" and s.carriage=? ");
            saleProdList.add(saleProd.getCarriage());
        }
        if (StringUtils.isNotBlank(saleProd.getProdSkin())){
            sql.append(" and s.prod_skin like ? ");
            saleProdList.add("%"+saleProd.getProdSkin()+"%");
        }
        if (StringUtils.isNotBlank(saleProd.getLocation())){
            sql.append(" and s.location like ? ");
            saleProdList.add("%"+saleProd.getLocation()+"%");
        }
        if (saleProd.getStatus()!=null && saleProd.getStatus()!=0){
            sql.append(" and s.status=? ");
            saleProdList.add(saleProd.getStatus());
        }
        sql.append(" )r where ROWNUM<=?"+
                " ) sale where rn>=?");
        saleProdList.add(page.getLastIndex());
        saleProdList.add(page.getFirstIndex());

        try (Connection conn = JdbcUtil.getConnection()) {// list.toArray
            List<SaleProd> saleProds = runner.query(conn,
                    sql.toString(),
                    new SaleProdListHandler(),
                    saleProdList.toArray());
            return saleProds;
        } catch (SQLException e) {
            logger.error("在售商品按条件查询失败。。。"+e.getMessage());
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 4.下架在线商品
     * @param saleProd
     * @return
     */
    public boolean deleteSaleProd(SaleProd saleProd){
        String sql = "update sale_prod set status =0 where id=?";
        try (Connection conn = JdbcUtil.getConnection()) {
            int row = runner.update(conn, sql,saleProd.getId());
            return row==1?true:false;
        } catch (SQLException e) {
            logger.error("下架在线商品失败。。。"+e.getMessage());
        }
        return false;
    }



}
